﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;

namespace YXShop.Web.admin.product
{
    public partial class product_import : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                YXShop.Common.PromptInfo.Popedom("001005006","对不起，您没有权限进行批量导入");
                YXShop.Common.SysParameter sp = new YXShop.Common.SysParameter();
                this.txtProductClass.Attributes.Add("readonly", "readonly");
                this.txtProductClass.Attributes.Add("onclick", "selectFile('Random_Productclass',new Array(" + this.hfcid.ClientID + "," + this.txtProductClass.ClientID + "),310,450,'" + sp.DummyPaht + "');");
                InitWebControl();
            }
        }

        private DataSet GetDataSetFromCSV(string filePath, string fileName)
        {
            string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
            strConn += filePath;                                                        //filePath, For example: C:\
            strConn += ";Extensions=asc,csv,tab,txt;";
            OdbcConnection objConn = new OdbcConnection(strConn);
            DataSet dsCSV = new DataSet();
            try
            {
                string strSql = "select * from [" +fileName+"]";                     //fileName, For example: 1.csv
                OdbcDataAdapter odbcCSVDataAdapter = new OdbcDataAdapter(strSql, objConn);
                odbcCSVDataAdapter.Fill(dsCSV, "Products");
                return dsCSV;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataSet GetDataSetFromXLS(string filePath, string fileName)
        {
            string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath + fileName + ";Extended Properties=Excel 8.0;";
            DataSet ds = new DataSet();
            try
            {
                OleDbConnection conNection = new OleDbConnection(conn);
                conNection.Open();
                string Sql = "select * from [Products$]";
                OleDbDataAdapter adp = new OleDbDataAdapter(Sql, conn);
                adp.Fill(ds, "Products");
                conNection.Close();
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #region 验证
        private void InitWebControl()
        {
            ChangeHope.WebPage.WebControl.Validate(this.txtProductClass, "选择所属分类", "isnull", "必填", "该项为必填项");
            this.Form.Attributes.Add("onsubmit", "return CheckForm()");
        }
        #endregion
        protected void lbtnSave_Click(object sender, EventArgs e)
        {
            if (this.fuExcecl.PostedFile.ContentLength == 0)
            {
                ChangeHope.WebPage.BasePage.PageError("请选择您要上传的Excel文件!", "product_import.aspx");
                return;
            }
            string filename = "";
            if (this.ddlType.SelectedValue == "1")
            {
                filename = System.Guid.NewGuid().ToString() + ".xls";
            }
            if (this.ddlType.SelectedValue == "2")
            {
                filename = System.Guid.NewGuid().ToString() + ".csv";
            }
            int j = 0;
            StringBuilder info = new StringBuilder();
            int count = 0;
            this.fuExcecl.Accept = "application/octet-stream,application/vnd.ms-excel";
            //try
            //{
                if (this.fuExcecl.Accept.Contains(this.fuExcecl.PostedFile.ContentType))//检测当前的文件类型是不是在被允许的列表中
                {
                    string classId = string.Empty;

                    #region 链接上传了的 Excel
                    this.fuExcecl.PostedFile.SaveAs(@Server.MapPath("file\\") + filename);
                    DataSet ds = null;

                    if (this.ddlType.SelectedValue == "1")
                    {
                        ds = this.GetDataSetFromXLS(Server.MapPath("file\\"), filename);
                    }
                    if (this.ddlType.SelectedValue == "2")
                    {
                        ds = this.GetDataSetFromCSV(Server.MapPath("file\\"), filename);
                    }
                    #endregion

                    count = ds.Tables["Products"].Rows.Count;
                    for (int i = 0; i < count; i++)
                    {
                        YXShop.BLL.Product.ProductInfo bll = new YXShop.BLL.Product.ProductInfo();
                        YXShop.Model.Product.ProductInfo model = new YXShop.Model.Product.ProductInfo();
                        string productNo = ds.Tables["Products"].Rows[i]["商品货号"].ToString().Trim();
                        if (!string.IsNullOrEmpty(productNo))
                        {
                            DataTable dt = bll.DTGetListWhere(" and pro_NO='" + productNo + "'");
                            if (dt.Rows.Count > 0)
                            {
                                info.Append("第" + (i + 2) + "行的[商品货号]不能重复，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        #region 卓列读取Excel数据
                        string proName = string.Empty;
                        decimal proWeigth = 0;
                        int isAudit = 0;
                        DateTime autoUp = System.DateTime.Now;
                        DateTime autoDown = Convert.ToDateTime("2020-01-01");
                        decimal donateIntegral = 0;
                        int auditCount = 0;
                        decimal marketPrice = 0;
                        #region 参数验证 i+2 索引从0开始 外加一个标题行

                        proName = ds.Tables["Products"].Rows[i]["商品名称"].ToString();
                        if (proName.Trim() == string.Empty)
                        {
                            info.Append("第" + (i + 2) + "行的[商品名称]为空了，请修正后重新上传！<br>");
                            continue;
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["商品重量"].ToString().Trim()))
                        {
                            try
                            {
                                proWeigth = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["商品重量"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append("第" + (i + 2) + "行的[商品重量]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["是否上架销售"].ToString().Trim()))
                        {
                            try
                            {
                                isAudit = Convert.ToInt32(ds.Tables["Products"].Rows[i]["是否上架销售"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[是否上架销售]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["上架时间"].ToString().Trim()))
                        {
                            try
                            {
                                autoUp = Convert.ToDateTime(ds.Tables["Products"].Rows[i]["上架时间"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[上架时间]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["下架时间"].ToString().Trim()))
                        {
                            try
                            {
                                autoDown = Convert.ToDateTime(ds.Tables["Products"].Rows[i]["下架时间"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[下架时间]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["上架数量"].ToString().Trim()))
                        {
                            try
                            {
                                auditCount = Convert.ToInt32(ds.Tables["Products"].Rows[i]["上架数量"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[上架数量]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["赠送积分"].ToString().Trim()))
                        {
                            try
                            {
                                donateIntegral = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["赠送积分"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[赠送积分]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["市场售价"].ToString().Trim()))
                        {
                            try
                            {
                                marketPrice = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["市场售价"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[市场售价]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        decimal constPrice = 0;
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["商品成本价"].ToString().Trim()))
                        {
                            try
                            {
                                constPrice = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["商品成本价"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[商品成本价]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        int alarm = 0;
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["报警数量"].ToString().Trim()))
                        {
                            try
                            {
                                alarm = Convert.ToInt32(ds.Tables["Products"].Rows[i]["报警数量"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[报警数量]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        int stock = 0;
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["库存"].ToString().Trim()))
                        {
                            try
                            {
                                stock = Convert.ToInt32(ds.Tables["Products"].Rows[i]["库存"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[库存]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        decimal integralPrice = 0;
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["积分购买额度"].ToString().Trim()))
                        {
                            try
                            {
                                integralPrice = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["积分购买额度"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[积分购买额度]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                        decimal shopPrice = 0;
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["商城售价"].ToString().Trim()))
                        {
                            try
                            {
                                shopPrice = Convert.ToDecimal(ds.Tables["Products"].Rows[i]["商城售价"].ToString().Trim());
                            }
                            catch
                            {
                                info.Append( "第" + (i + 2) + "行的[商城售价]填写数据不正确，请修正后重新上传！<br>");
                                continue;
                            }
                        }
                       
                        #endregion
                        #endregion

                        ////开始导入信息到数据
                        YXShop.BLL.Product.ProductUnit UnitBll = new YXShop.BLL.Product.ProductUnit();
                        YXShop.Model.Product.ProductUnit UnitModel = new YXShop.Model.Product.ProductUnit();

                        #region 加入商品表
                        model.pro_CID = this.hfcid.Value != "" ? this.hfcid.Value : "0";
                        model.pro_Type = 0;
                        model.pro_Property = "";
                        model.pro_Parameters = "";
                        model.pro_Name = proName;
                        model.pro_NO = ds.Tables["Products"].Rows[i]["商品货号"].ToString().Trim() == "" ? this.GoodsNO() : ds.Tables["Products"].Rows[i]["商品货号"].ToString().Trim();
                        string brandName = ds.Tables["Products"].Rows[i]["商品品牌"].ToString().Trim();
                        #region 商品品牌
                        int brandId = 0;
                        if (!string.IsNullOrEmpty(brandName))
                        {
                            YXShop.BLL.Product.ProductBrand brandBll = new YXShop.BLL.Product.ProductBrand();
                            YXShop.Model.Product.ProductBrand brandModel = new YXShop.Model.Product.ProductBrand();
                            YXShop.Model.Product.ProductBrand getBrand = brandBll.GetModelName(brandName);
                            if (getBrand == null)
                            {
                                brandModel.CommonTypes = 0;
                                brandModel.Name = brandName;
                                brandModel.Description = "";
                                brandModel.Attirbute = "ff";
                                brandModel.Isrecommend = 0;
                                brandModel.Sort = 0;
                                brandModel.Images = "";
                                brandId = brandBll.Add(brandModel);
                            }
                            else
                            {
                                brandId = getBrand.ID;
                            }
                        }
                        #endregion
                        model.pro_BrandID = brandId;
                        #region 商品单位
                        string unitName = ds.Tables["Products"].Rows[i]["商品单位"].ToString().Trim();
                        if (!string.IsNullOrEmpty(unitName))
                        {
                            List<YXShop.Model.Product.ProductUnit> unitList = UnitBll.GetAll(" name='" + unitName+"'");
                            if (unitList.Count < 1)
                            {
                                UnitModel.Name = unitName;
                                UnitModel.Sort = 0;
                                UnitBll.Add(UnitModel);
                            }

                        }
                        else
                        {
                            unitName = "件";
                        }
                        #endregion
                        model.pro_Unit = unitName;
                        model.pro_Weight = proWeigth;
                        model.pro_Audit = isAudit;
                        model.pro_AutoUp = autoUp;
                        model.pro_AutoDown = autoDown;
                        model.pro_AuditCount = auditCount;
                        model.pro_DonateIntegral = donateIntegral;
                        model.pro_MarketPrice = marketPrice;
                        model.pro_CostPrice = constPrice;
                        model.pro_ContentTemplate = !string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["内容页模板路径"].ToString().Trim()) ? ds.Tables["Products"].Rows[i]["内容页模板路径"].ToString().Trim() : "";
                        model.pro_Alarm = alarm;
                        model.pro_Area = "";
                        model.pro_PutoutID = 0;
                        model.pro_PutoutType = 0;
                        string Pro_State = "0|0|0|0|0|0|0|";
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["商品标签"].ToString().Trim()))
                        {
                            Pro_State = ds.Tables["Products"].Rows[i]["商品标签"].ToString().Trim();
                        }
                        #endregion
                        model.pro_State = Pro_State;
                        #region 商品标识：团购|拍卖|批发
                        string Pro_Designation = "1|0|0|0|0|";
                        if (!string.IsNullOrEmpty(ds.Tables["Products"].Rows[i]["商品标识"].ToString().Trim()))
                        {
                            Pro_Designation = ds.Tables["Products"].Rows[i]["商品标识"].ToString().Trim();
                        }
                        #endregion
                        model.pro_Designation = Pro_Designation;
                        model.pro_Stock = stock;
                        model.pro_IntegralPrice = integralPrice;
                        model.pro_ShopPrice = shopPrice;
                        model.pro_RatingDiscount = "";
                        model.pro_Original = ds.Tables["Products"].Rows[i]["商品原始图"].ToString().Trim();
                        model.pro_Images = ds.Tables["Products"].Rows[i]["商品图片"].ToString().Trim();
                        model.pro_Thumbnail = ds.Tables["Products"].Rows[i]["商品缩略图"].ToString().Trim();
                        model.pro_Synopsis = ds.Tables["Products"].Rows[i]["简单描述"].ToString().Trim();
                        model.pro_Content = ds.Tables["Products"].Rows[i]["详细描述"].ToString().Trim();
                        model.pro_RelatingArtID = "";
                        model.pro_RelatingproID = "";
                        model.pro_MetaKeywords = ds.Tables["Products"].Rows[i]["优化关键字"].ToString().Trim();
                        model.pro_MetaDescription = ds.Tables["Products"].Rows[i]["优化描述"].ToString().Trim();
                        model.pro_MetaTitle = ds.Tables["Products"].Rows[i]["优化标题"].ToString().Trim();
                        model.pro_UpdateTime = System.DateTime.Now;
                        int productId = bll.Add(model);
                        j++;
                    }
                    ChangeHope.WebPage.BasePage.PageRight("操作成功，一共"+count+"条数据，您成功导入"+j.ToString()+"条商品。", "product_import.aspx", "<li>" + info.ToString() + "</li>");
                }
                else
                {
                    ChangeHope.WebPage.BasePage.PageError("你选择的文件不是Excel或者版本过低!","product_import.aspx");
                    return;
                }
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    System.IO.File.Delete(@Server.MapPath("file/") + filename);

            //}
        }
        #region 商品货号
        /// <summary>
        /// 商品货号
        /// </summary>
        /// <returns></returns>
        private string GoodsNO()
        {
            string reStr = string.Empty;
            reStr = "G" + System.DateTime.Now.ToString("yyyyMMddHHmmss");
            return reStr += ChangeHope.Common.DEncryptHelper.GetRandWord(6);
        }
        #endregion
    }
}
